Imports System.Data.SqlClient

Friend Class DBScanFunction
    Inherits DBBase

    Private moServer As SQLAutoDocLib.Server
    Private moDatabase As SQLAutoDocLib.Database

    Public Event ItemScanStart(ByVal lServerID As Long, ByVal lDatabaseID As Long, ByVal lSessionID As Long, ByVal lFunctionID As Long)
    Public Event ItemScanComplete(ByVal lSessionID As Long, ByVal lFunctionID As Long, ByVal oEndDate As Date)

    Public Sub New( _
                ByVal oServer As SQLAutoDocLib.Server, _
                ByVal oDatabase As SQLAutoDocLib.Database)

        moServer = oServer
        moDatabase = oDatabase
    End Sub

    Public Function Scan(ByVal lSessionID As Long) As Boolean
        Dim bChangesFound As Boolean = False

        Dim oFactory As New Function_Factory
        Dim oAllFunctions As Collection = oFactory.GetAllFunctionsForDatabase(moDatabase.ID)

        For Each oFunction As SQLAutoDocLib.Function In oAllFunctions
            oFunction.IsDeleted = True
            oFunction.Save()
        Next

        Dim iAlteredFunctions As Integer = 0
        Dim bFunctionsAddedOrUpdated As Boolean = False
        RefreshFunctionsForDatabase(lSessionID, oAllFunctions, iAlteredFunctions)
        If iAlteredFunctions > 0 Then bFunctionsAddedOrUpdated = True

        If bFunctionsAddedOrUpdated = False Then
            '---Were any views deleted?
            For Each oFunction As SQLAutoDocLib.Function In oAllFunctions
                If oFunction.IsDeleted = True Then
                    oFunction.DatabaseRevID = moDatabase.RevID
                    oFunction.Save(bForceAdd:=True)
                    bChangesFound = True
                End If
            Next
        Else
            bChangesFound = True
        End If

        Return bChangesFound
    End Function

    Private Sub RefreshFunctionsForDatabase( _
                    ByVal lSessionID As Long, _
                    ByRef oAllFunctions As Collection, _
                    ByRef iAlteredFunctions As Integer)

        Dim sSQL = "select distinct"
        sSQL = sSQL & "     a.id,"
        sSQL = sSQL & "     b.name 'FunctionOwner',"
        sSQL = sSQL & "     a.name 'FunctionName'"
        sSQL = sSQL & " from "
        sSQL = sSQL & " dbo.sysobjects a"
        sSQL = sSQL & " join dbo.sysusers b on b.uid=a.uid"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     a.type in ('FN','TF')"  '---Functions and table-functions
        sSQL = sSQL & "     and a.category!=2"

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL, moDatabase.DecryptedConnectionString)

        Dim oLoginFactory As New Login_Factory
        Dim oFunctionFactory As New Function_Factory

        Dim bFunctionAdded As Boolean = False

        For Each oRow As DataRow In oData.Rows
            Dim oLogin As SQLAutoDocLib.Login = oLoginFactory.GetLoginByName(moDatabase.ID, oRow.Item("FunctionOwner"))
            Dim oFunction As SQLAutoDocLib.Function = oFunctionFactory.GetFunctionByName(moDatabase.ID, oLogin.ID, oRow.Item("FunctionName"))

            If oFunction Is Nothing Then
                '---Found a new view
                bFunctionAdded = True

                oFunction = New SQLAutoDocLib.Function
                With oFunction
                    .Name = oRow.Item("FunctionName")
                    .OwnerID = oLogin.ID
                    .OID = oRow.Item("id")
                    .DatabaseID = moDatabase.ID
                    .DatabaseRevID = moDatabase.RevID
                End With

                iAlteredFunctions = iAlteredFunctions + 1
            Else
                bFunctionAdded = False
            End If

            If oAllFunctions.Contains(CStr(oFunction.ID)) Then
                oAllFunctions(CStr(oFunction.ID)).IsDeleted = False
            End If

            oFunction.IsDeleted = False

            oFunction.Save()

            RaiseEvent ItemScanStart(moDatabase.ServerID, moDatabase.ID, lSessionID, oFunction.ID)

            oFunction.DatabaseRevID = moDatabase.RevID
            Dim bFunctionUpdated As Boolean = oFunction.Scan()
            If bFunctionUpdated = True Then
                iAlteredFunctions = iAlteredFunctions + 1
            End If

            RaiseEvent ItemScanComplete(lSessionID, oFunction.ID, Now)

        Next
    End Sub
End Class
